﻿CREATE FUNCTION [dbo].[fs_Bankruptcy_Base](
	@StateID int
)RETURNS TABLE
AS

RETURN 
SELECT     L_B.LoanID, CF.UsixID, CF.[Bankruptcy Filed Y_N?], CF.[Bankruptcy Type 07_13?], L_B.FirstName AS [First Name], L_B.LastName AS [Last Name], 
                      CONVERT(varchar, C.StatusDate, 111) AS [Date In], DATEDIFF(dd, C.StatusDate, GETDATE()) AS [Days In], L_B.Status AS LoanStatus, 
                      C.StatusUser AS [User], t_BankruptcyCase.CaseNumber, t_BankruptcyCase.Chapter, t_BankruptcyCase.DatePulled, t_BankruptcyCase.Filed, 
                      t_BankruptcyCase.Discharged, t_BankruptcyCase.Dismissed, t_BankruptcyCase.Joint, t_BankruptcyCase.PendingStatus
FROM         v_List_Base AS L_B INNER JOIN
                      t_Bankruptcy AS C ON C.LoanID = L_B.LoanID LEFT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF ON C.LoanID = CF.LoanID INNER JOIN
                      dbo.fs_Bankruptcy_Read(DEFAULT, @StateID) AS fs_Loans ON L_B.LoanID = fs_Loans.LoanID LEFT OUTER JOIN
                      t_BankruptcyCase ON L_B.SSN = t_BankruptcyCase.SSN
/*
INNER JOIN
                          (SELECT     LoanID, MenuID, COUNT(*) AS Count
                            FROM          t_Loan_Menus
                            GROUP BY LoanID, MenuID
                            HAVING      (MenuID = @MenuID)) AS MENU_COUNT ON L_B.LoanID = MENU_COUNT.LoanID LEFT OUTER JOIN
                          (SELECT     LoanID, MAX(DateStamp) AS DateStamp
                            FROM          t_Notes AS t_Notes_1
                            GROUP BY LoanID) AS t_Notes ON L_B.LoanID = t_Notes.LoanID
*/




